package com.common.www.service.tabulation;


import org.springframework.boot.base.jpa.service.BaseLogicService;
import com.common.www.bean.entity.tabulation.XfgcTabulation;
import com.common.www.bean.vo.xfgc.StatisticalXfgcBiddingVo;
import com.common.www.repository.tabulation.XfgcTabulationRepository;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.math.BigDecimal;
import java.util.*;
import java.util.stream.Collectors;

@Service
@Transactional
public class XfgcTabulationService extends BaseLogicService<XfgcTabulation,Long> {
    @Autowired
    private XfgcTabulationRepository repository;
    @Override
    public XfgcTabulationRepository getRepository() {
        return repository;
    }

    /**
     * 导入供应商清单
     *
     * @param workbook
     * @return
     * @throws Exception
     */
    public List<XfgcTabulation> importSimulate(Workbook workbook) throws Exception {
        Sheet sheet = null;
        if (workbook.getNumberOfSheets() > 0) {
            sheet = workbook.getSheetAt(0);
        }
        if (sheet == null) {
            throw new Exception("未找到导入数据,请检查文档");
        }
        List<XfgcTabulation> list = new ArrayList<>();

        BigDecimal qiYeCostFillIn = BigDecimal.ZERO;
        BigDecimal shuiJinFillIn = BigDecimal.ZERO;
        for (int rowIndex = 4; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
            if (rowIndex == 5){
                continue;
            }
//            System.out.println("第 " + rowIndex + " 行");
            String r0 = null;
            String r1 = null;
            String r2 = null;
            String r3 = null;
            String r4 = null;
            String r5 = null;
            String r6 = null;
            String r7 = null;
            String r8 = null;
            String r9 = null;
            String r10 = null;
            String r11 = null;
            String r12 = null;
            String r13 = null;
            String r14 = null;
            String r15 = null;
            String r16 = null;
            String r17 = null;
            String r18 = null;
            String r19 = null;
            String r20 = null;
            String r21 = null;
            String r22 = null;
            String r23 = null;
            String r24 = null;
            String r25 = null;
            String r26 = null;
            String r27 = null;
            String r28 = null;
            String r29 = null;
            String r30 = null;
            String r31 = null;
            String r32 = null;
            String r33 = null;
            String r34 = null;
            String r35 = null;
            String r36 = null;
            String r37 = null;
            String r38 = null;
            String r39 = null;

            Row row = sheet.getRow(rowIndex);
            int numMergedRegions = sheet.getNumMergedRegions();
            if (row == null) {
                break;
            }
            Iterator<Cell> cellIterator = row.cellIterator();
            while (cellIterator.hasNext()) {
                Cell cell = cellIterator.next();
                cell.setCellType(CellType.STRING);
//                String value = cell.getStringCellValue();
                String value = getCellValue(cell, sheet, numMergedRegions);

                if (cell.getColumnIndex() == 0)
                    r0 = value.trim();
                if (cell.getColumnIndex() == 1)
                    r1 = value.trim();
                if (cell.getColumnIndex() == 2)
                    r2 = value.trim();
                if (cell.getColumnIndex() == 3)
                    r3 = value.trim();
                if (cell.getColumnIndex() == 4)
                    r4 = value.trim();
                if (cell.getColumnIndex() == 5)
                    r5 = value.trim();
                if (cell.getColumnIndex() == 6)
                    r6 = value.trim();
                if (cell.getColumnIndex() == 7)
                    r7 = value.trim();
                if (cell.getColumnIndex() == 8)
                    r8 = value.trim();
                if (cell.getColumnIndex() == 9)
                    r9 = value.trim();
                if (cell.getColumnIndex() == 10)
                    r10 = value.trim();
                if (cell.getColumnIndex() == 11)
                    r11 = value.trim();
                if (cell.getColumnIndex() == 12)
                    r12 = value.trim();
                if (cell.getColumnIndex() == 13)
                    r13 = value.trim();
                if (cell.getColumnIndex() == 14)
                    r14 = value.trim();
                if (cell.getColumnIndex() == 15)
                    r15 = value.trim();
                if (cell.getColumnIndex() == 16)
                    r16 = value.trim();
                if (cell.getColumnIndex() == 17)
                    r17 = value.trim();
                if (cell.getColumnIndex() == 18)
                    r18 = value.trim();
                if (cell.getColumnIndex() == 19)
                    r19 = value.trim();
                if (cell.getColumnIndex() == 20)
                    r20 = value.trim();
                if (cell.getColumnIndex() == 21)
                    r21 = value.trim();
                if (cell.getColumnIndex() == 22)
                    r22 = value.trim();
                if (cell.getColumnIndex() == 23)
                    r23 = value.trim();
                if (cell.getColumnIndex() == 24)
                    r24 = value.trim();
                if (cell.getColumnIndex() == 25)
                    r25 = value.trim();
                if (cell.getColumnIndex() == 26)
                    r26 = value.trim();
                if (cell.getColumnIndex() == 27)
                    r27 = value.trim();
                if (cell.getColumnIndex() == 28)
                    r28 = value.trim();
                if (cell.getColumnIndex() == 29)
                    r29 = value.trim();
                if (cell.getColumnIndex() == 30)
                    r30 = value.trim();
                if (cell.getColumnIndex() == 31)
                    r31 = value.trim();
                if (cell.getColumnIndex() == 32)
                    r32 = value.trim();
                if (cell.getColumnIndex() == 33)
                    r33 = value.trim();
                if (cell.getColumnIndex() == 34)
                    r34 = value.trim();
                if (cell.getColumnIndex() == 35)
                    r35 = value.trim();
                if (cell.getColumnIndex() == 36)
                    r36 = value.trim();
                if (cell.getColumnIndex() == 37)
                    r37 = value.trim();
                if (cell.getColumnIndex() == 38)
                    r38 = value.trim();
                if (cell.getColumnIndex() == 39)
                    r39 = value.trim();

            }
            if (rowIndex == 4){
                qiYeCostFillIn = (r26 != null && !r26.isEmpty()) ? BigDecimal.valueOf(Double.parseDouble(r26)) : null;
                shuiJinFillIn = (r27 != null && !r27.isEmpty()) ? BigDecimal.valueOf(Double.parseDouble(r27)) : null;
            }else {
                XfgcTabulation io = new XfgcTabulation();
                io.setCodeNumber(r0);
                io.setName(r1);
                io.setXiTongFenLei(r2);
                io.setFenXiFenLei1(r3);
                io.setFenXiFenLei2(r4);
                io.setModelCode(r5);
                io.setTeZhengNeiRong(r6);
                io.setUnit(r7);
                io.setBrand(r8);
                io.setZhuCaiGongYing(r9);
                io.setGongChengLiangHuiZong(r10 != null && !r10.isEmpty() ? new BigDecimal(r10) : null);
                io.setGongChengLiangDiXiShi(r11 != null && !r11.isEmpty() ? new BigDecimal(r11) : null);
                io.setGongChengLiangGaoCeng(r12 != null && !r12.isEmpty() ? new BigDecimal(r12) : null);
                io.setGongChengLiangXiaoGaoCeng(r13 != null && !r13.isEmpty() ? new BigDecimal(r13) : null);
                io.setGongChengLiangYangFang(r14 != null && !r14.isEmpty() ? new BigDecimal(r14) : null);
                io.setGongChengLiangShangYe(r15 != null && !r15.isEmpty() ? new BigDecimal(r15) : null);
                io.setGongChengLiangZongPing(r16 != null && !r16.isEmpty() ? new BigDecimal(r16) : null);
                io.setGongChengLiangBieShu(r17 != null && !r17.isEmpty() ? new BigDecimal(r17) : null);
                io.setGongChengLiangGongYu(r18 != null && !r18.isEmpty() ? new BigDecimal(r18) : null);
                io.setGongChengLiangYouErYuan(r19 != null && !r19.isEmpty() ? new BigDecimal(r19) : null);
                io.setComprehensivePriceBhs(r20 != null && !r20.isEmpty() ? new BigDecimal(r20) : null);
                io.setComprehensivePriceHs(r21 != null && !r21.isEmpty() ? new BigDecimal(r21) : null);
                io.setRenGongCost(r22 != null && !r22.isEmpty() ? new BigDecimal(r22) : null);
                io.setZhuCaiCost(r23 != null && !r23.isEmpty() ? new BigDecimal(r23) : null);
                io.setFuCaiCost(r24 != null && !r24.isEmpty() ? new BigDecimal(r24) : null);
                io.setJiXieCost(r25 != null && !r25.isEmpty() ? new BigDecimal(r25) : null);
                io.setQiYeCost(r26 != null && !r26.isEmpty() ? new BigDecimal(r26) : null);
                io.setShuiJin(r27 != null && !r27.isEmpty() ? new BigDecimal(r27) : null);
                io.setTotalPriceBhs(r28 != null && !r28.isEmpty() ? new BigDecimal(r28) : null);
                io.setTotalPriceHs(r29 != null && !r29.isEmpty() ? new BigDecimal(r29) : null);
                io.setTotalPrice1(r30 != null && !r30.isEmpty() ? new BigDecimal(r30) : null);
                io.setTotalPrice2(r31 != null && !r31.isEmpty() ? new BigDecimal(r31) : null);
                io.setTotalPrice3(r32 != null && !r32.isEmpty() ? new BigDecimal(r32) : null);
                io.setTotalPrice4(r33 != null && !r33.isEmpty() ? new BigDecimal(r33) : null);
                io.setTotalPrice5(r34 != null && !r34.isEmpty() ? new BigDecimal(r34) : null);
                io.setTotalPrice6(r35 != null && !r35.isEmpty() ? new BigDecimal(r35) : null);
                io.setTotalPrice7(r36 != null && !r36.isEmpty() ? new BigDecimal(r36) : null);
                io.setTotalPrice8(r37 != null && !r37.isEmpty() ? new BigDecimal(r37) : null);
                io.setTotalPrice9(r38 != null && !r38.isEmpty() ? new BigDecimal(r38) : null);
                io.setRemarks(r39);
                io.setQiYeCostFillIn(qiYeCostFillIn);
                io.setShuiJinFillIn(shuiJinFillIn);
                list.add(io);
            }
        }

        return list;
    }

    /**
     * 获取指定单元格的值。如果单元格属于合并单元格，返回合并区域顶端单元格的值。
     *
     * @param cell 当前需要获取值的单元格
     * @param sheet 当前工作表
     * @param numMergedRegions 合并单元格区域数量
     * @return 单元格的字符串值，如果单元格为空或无效，则返回null
     */
    private String getCellValue(Cell cell, Sheet sheet, int numMergedRegions) {
        for (int i = 0; i < numMergedRegions; i++) {
            CellRangeAddress mergedRegion = sheet.getMergedRegion(i);
            if (mergedRegion.isInRange(cell.getRowIndex(), cell.getColumnIndex())) {
                // 获取合并单元格的顶端单元格的值
                Cell topCell = sheet.getRow(mergedRegion.getFirstRow()).getCell(mergedRegion.getFirstColumn());
                return getStringCellValue(topCell);
            }
        }
        return getStringCellValue(cell);
    }

    /**
     * 将单元格类型设置为字符串并返回其字符串值。
     *
     * @param cell 当前需要获取值的单元格
     * @return 单元格的字符串值，如果单元格为空，则返回null
     */
    private String getStringCellValue(Cell cell) {
        if (cell == null) {
            return null;
        }
        cell.setCellType(CellType.STRING);
        return cell.getStringCellValue();
    }


    /**
     * 创建单元格
     * @param row
     * @param index
     * @param style
     * @return
     */
    public static Cell createCellAddStype(Row row, Integer index, CellStyle style) {
        Cell hssfCell = row.createCell(index);
        hssfCell.setCellStyle(style);
        return hssfCell;
    }

    /**
     * 导出清单
     * @param biddingCompanyId
     * @param jieDuan
     * @param fileName
     * @return
     */
    public XSSFWorkbook exportQd(Long biddingCompanyId, Integer jieDuan,String fileName) {
        HashMap hashMap = new HashMap();
        hashMap.put("flag", 1);
        hashMap.put("xfgcBiddingCompany.id", biddingCompanyId);
        hashMap.put("jieDuan", jieDuan);
        List<XfgcTabulation> items = getListLogic(hashMap, null);
        BigDecimal qiYeCostFillIn = BigDecimal.ZERO;
        BigDecimal shuiJinFillIn = BigDecimal.ZERO;
        if (items.size() > 0){
            qiYeCostFillIn = items.get(0).getQiYeCostFillIn();
            shuiJinFillIn = items.get(0).getShuiJinFillIn();
        }
        items.sort(Comparator.comparing(XfgcTabulation::getNameType1));

        Map<String, List<XfgcTabulation>> nameType1List = items.stream()
                .collect(Collectors.groupingBy(XfgcTabulation::getNameType1));

        XSSFWorkbook workbook = new XSSFWorkbook();
        String sheetName = fileName + "供应商清单";
        Sheet sheet = workbook.createSheet(sheetName);
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER); // 居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 居中
        // 创建日期格式
        CellStyle cellDateStyle = workbook.createCellStyle();
        CreationHelper createHelper = workbook.getCreationHelper();
        cellDateStyle.setDataFormat(createHelper.createDataFormat().getFormat("yyyy-mm-dd hh:mm:ss"));
        Row row0 = sheet.createRow(0);
        createCellAddStype(row0, 0, cellStyle).setCellValue("工程量清单综合单价计价表");
        CellRangeAddress titleRegion = new CellRangeAddress(0, 0, 0, 39);// 下标从0开始 起始行号，终止行号， 起始列号，终
        sheet.addMergedRegionUnsafe(titleRegion);
        //边框
        cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
        cellStyle.setBorderLeft(BorderStyle.THIN);//左边框
        cellStyle.setBorderTop(BorderStyle.THIN);//上边框
        cellStyle.setBorderRight(BorderStyle.THIN);//右边框
        cellDateStyle.setBorderBottom(BorderStyle.THIN); //下边框
        cellDateStyle.setBorderLeft(BorderStyle.THIN);//左边框
        cellDateStyle.setBorderTop(BorderStyle.THIN);//上边框
        cellDateStyle.setBorderRight(BorderStyle.THIN);//右边框

        Row row1 = sheet.createRow(1);
        createCellAddStype(row1, 0, cellStyle).setCellValue("序号");
        createCellAddStype(row1, 1, cellStyle).setCellValue("项目名称");
        createCellAddStype(row1, 2, cellStyle).setCellValue("系统分类");
        createCellAddStype(row1, 3, cellStyle).setCellValue("分析分类");
        createCellAddStype(row1, 4, cellStyle).setCellValue("分析分类");
        createCellAddStype(row1, 5, cellStyle).setCellValue("规格型号");
        createCellAddStype(row1, 6, cellStyle).setCellValue("项目特征及主要工程内容");
        createCellAddStype(row1, 7, cellStyle).setCellValue("单位");
        createCellAddStype(row1, 8, cellStyle).setCellValue("品牌");
        createCellAddStype(row1, 9, cellStyle).setCellValue("主材供应方式");
        createCellAddStype(row1, 10, cellStyle).setCellValue("工程量");
        createCellAddStype(row1, 11, cellStyle).setCellValue("工程量");
        createCellAddStype(row1, 12, cellStyle).setCellValue("工程量");
        createCellAddStype(row1, 13, cellStyle).setCellValue("工程量");
        createCellAddStype(row1, 14, cellStyle).setCellValue("工程量");
        createCellAddStype(row1, 15, cellStyle).setCellValue("工程量");
        createCellAddStype(row1, 16, cellStyle).setCellValue("工程量");
        createCellAddStype(row1, 17, cellStyle).setCellValue("工程量");
        createCellAddStype(row1, 18, cellStyle).setCellValue("工程量");
        createCellAddStype(row1, 19, cellStyle).setCellValue("工程量");
        createCellAddStype(row1, 20, cellStyle).setCellValue("综合单价1");
        createCellAddStype(row1, 21, cellStyle).setCellValue("综合单价2");
        createCellAddStype(row1, 22, cellStyle).setCellValue("综合单价组成");
        createCellAddStype(row1, 23, cellStyle).setCellValue("综合单价组成");
        createCellAddStype(row1, 24, cellStyle).setCellValue("综合单价组成");
        createCellAddStype(row1, 25, cellStyle).setCellValue("综合单价组成");
        createCellAddStype(row1, 26, cellStyle).setCellValue("综合单价组成");
        createCellAddStype(row1, 27, cellStyle).setCellValue("综合单价组成");
        createCellAddStype(row1, 28, cellStyle).setCellValue("全业态合计");
        createCellAddStype(row1, 29, cellStyle).setCellValue("全业态合计");
        createCellAddStype(row1, 30, cellStyle).setCellValue("地下室合计");
        createCellAddStype(row1, 31, cellStyle).setCellValue("高层合计");
        createCellAddStype(row1, 32, cellStyle).setCellValue("小高层合计");
        createCellAddStype(row1, 33, cellStyle).setCellValue("洋房合计");
        createCellAddStype(row1, 34, cellStyle).setCellValue("商业合计");
        createCellAddStype(row1, 35, cellStyle).setCellValue("总平合计");
        createCellAddStype(row1, 36, cellStyle).setCellValue("别墅合计");
        createCellAddStype(row1, 37, cellStyle).setCellValue("公寓合计");
        createCellAddStype(row1, 38, cellStyle).setCellValue("幼儿园合计");
        createCellAddStype(row1, 39, cellStyle).setCellValue("备注");
        Row row2 = sheet.createRow(2);
        createCellAddStype(row2, 0, cellStyle).setCellValue("序号");
        createCellAddStype(row2, 1, cellStyle).setCellValue("项目名称");
        createCellAddStype(row2, 2, cellStyle).setCellValue("系统分类");
        createCellAddStype(row2, 3, cellStyle).setCellValue("分析分类");
        createCellAddStype(row2, 4, cellStyle).setCellValue("分析分类");
        createCellAddStype(row2, 5, cellStyle).setCellValue("规格型号");
        createCellAddStype(row2, 6, cellStyle).setCellValue("项目特征及主要工程内容");
        createCellAddStype(row2, 7, cellStyle).setCellValue("单位");
        createCellAddStype(row2, 8, cellStyle).setCellValue("品牌");
        createCellAddStype(row2, 9, cellStyle).setCellValue("主材供应方式");
        createCellAddStype(row2, 10, cellStyle).setCellValue("工程量");
        createCellAddStype(row2, 11, cellStyle).setCellValue("工程量");
        createCellAddStype(row2, 12, cellStyle).setCellValue("工程量");
        createCellAddStype(row2, 13, cellStyle).setCellValue("工程量");
        createCellAddStype(row2, 14, cellStyle).setCellValue("工程量");
        createCellAddStype(row2, 15, cellStyle).setCellValue("工程量");
        createCellAddStype(row2, 16, cellStyle).setCellValue("工程量");
        createCellAddStype(row2, 17, cellStyle).setCellValue("工程量");
        createCellAddStype(row2, 18, cellStyle).setCellValue("工程量");
        createCellAddStype(row2, 19, cellStyle).setCellValue("工程量");
        createCellAddStype(row2, 20, cellStyle).setCellValue("综合单价1");
        createCellAddStype(row2, 21, cellStyle).setCellValue("综合单价2");
        createCellAddStype(row2, 22, cellStyle).setCellValue("直接工程费");
        createCellAddStype(row2, 23, cellStyle).setCellValue("直接工程费");
        createCellAddStype(row2, 24, cellStyle).setCellValue("直接工程费");
        createCellAddStype(row2, 25, cellStyle).setCellValue("直接工程费");
        createCellAddStype(row2, 26, cellStyle).setCellValue("企业管理费、利润、规费等Ⓒ%");
        createCellAddStype(row2, 27, cellStyle).setCellValue("增值税专用发票税额Ⓓ%");
        createCellAddStype(row2, 28, cellStyle).setCellValue("合价");
        createCellAddStype(row2, 29, cellStyle).setCellValue("合价");
        createCellAddStype(row2, 30, cellStyle).setCellValue("合价1");
        createCellAddStype(row2, 31, cellStyle).setCellValue("合价2");
        createCellAddStype(row2, 32, cellStyle).setCellValue("合价3");
        createCellAddStype(row2, 33, cellStyle).setCellValue("合价4");
        createCellAddStype(row2, 34, cellStyle).setCellValue("合价5");
        createCellAddStype(row2, 35, cellStyle).setCellValue("合价6");
        createCellAddStype(row2, 36, cellStyle).setCellValue("合价7");
        createCellAddStype(row2, 37, cellStyle).setCellValue("合价8");
        createCellAddStype(row2, 38, cellStyle).setCellValue("合价9");
        createCellAddStype(row2, 39, cellStyle).setCellValue("备注");

        Row row3 = sheet.createRow(3);
        createCellAddStype(row3, 0, cellStyle).setCellValue("序号");
        createCellAddStype(row3, 1, cellStyle).setCellValue("项目名称");
        createCellAddStype(row3, 2, cellStyle).setCellValue("系统分类");
        createCellAddStype(row3, 3, cellStyle).setCellValue("分析分类");
        createCellAddStype(row3, 4, cellStyle).setCellValue("分析分类");
        createCellAddStype(row3, 5, cellStyle).setCellValue("规格型号");
        createCellAddStype(row3, 6, cellStyle).setCellValue("项目特征及主要工程内容");
        createCellAddStype(row3, 7, cellStyle).setCellValue("单位");
        createCellAddStype(row3, 8, cellStyle).setCellValue("品牌");
        createCellAddStype(row3, 9, cellStyle).setCellValue("主材供应方式");
        createCellAddStype(row3, 10, cellStyle).setCellValue("工程量汇总");
        createCellAddStype(row3, 11, cellStyle).setCellValue("地下室");
        createCellAddStype(row3, 12, cellStyle).setCellValue("高层");
        createCellAddStype(row3, 13, cellStyle).setCellValue("小高层");
        createCellAddStype(row3, 14, cellStyle).setCellValue("洋房");
        createCellAddStype(row3, 15, cellStyle).setCellValue("商业");
        createCellAddStype(row3, 16, cellStyle).setCellValue("总平");
        createCellAddStype(row3, 17, cellStyle).setCellValue("别墅");
        createCellAddStype(row3, 18, cellStyle).setCellValue("公寓");
        createCellAddStype(row3, 19, cellStyle).setCellValue("幼儿园");
        createCellAddStype(row3, 20, cellStyle).setCellValue("新项目不含增值税");
        createCellAddStype(row3, 21, cellStyle).setCellValue("新项目含增值税");
        createCellAddStype(row3, 22, cellStyle).setCellValue("人工费");
        createCellAddStype(row3, 23, cellStyle).setCellValue("主材费");
        createCellAddStype(row3, 24, cellStyle).setCellValue("辅材及其他材料费");
        createCellAddStype(row3, 25, cellStyle).setCellValue("机械费");
        createCellAddStype(row3, 26, cellStyle).setCellValue(qiYeCostFillIn.toString());
        createCellAddStype(row3, 27, cellStyle).setCellValue(shuiJinFillIn.toString());
        createCellAddStype(row3, 28, cellStyle).setCellValue("不含增值税");
        createCellAddStype(row3, 29, cellStyle).setCellValue("含增值税");
        createCellAddStype(row3, 30, cellStyle).setCellValue("含增值税");
        createCellAddStype(row3, 31, cellStyle).setCellValue("含增值税");
        createCellAddStype(row3, 32, cellStyle).setCellValue("含增值税");
        createCellAddStype(row3, 33, cellStyle).setCellValue("含增值税");
        createCellAddStype(row3, 34, cellStyle).setCellValue("含增值税");
        createCellAddStype(row3, 35, cellStyle).setCellValue("含增值税");
        createCellAddStype(row3, 36, cellStyle).setCellValue("含增值税");
        createCellAddStype(row3, 37, cellStyle).setCellValue("含增值税");
        createCellAddStype(row3, 38, cellStyle).setCellValue("含增值税");
        createCellAddStype(row3, 39, cellStyle).setCellValue("备注");

        Row row4 = sheet.createRow(4);
        createCellAddStype(row4, 0, cellStyle).setCellValue("序号");
        createCellAddStype(row4, 1, cellStyle).setCellValue("项目名称");
        createCellAddStype(row4, 2, cellStyle).setCellValue("系统分类");
        createCellAddStype(row4, 3, cellStyle).setCellValue("分析分类");
        createCellAddStype(row4, 4, cellStyle).setCellValue("分析分类");
        createCellAddStype(row4, 5, cellStyle).setCellValue("规格型号");
        createCellAddStype(row4, 6, cellStyle).setCellValue("项目特征及主要工程内容");
        createCellAddStype(row4, 7, cellStyle).setCellValue("单位");
        createCellAddStype(row4, 8, cellStyle).setCellValue("品牌");
        createCellAddStype(row4, 9, cellStyle).setCellValue("主材供应方式");
        createCellAddStype(row4, 10, cellStyle).setCellValue("A=A1+A2+A3+A4+A5+A6+A7+A8+A9");
        createCellAddStype(row4, 11, cellStyle).setCellValue("A1");
        createCellAddStype(row4, 12, cellStyle).setCellValue("A2");
        createCellAddStype(row4, 13, cellStyle).setCellValue("A3");
        createCellAddStype(row4, 14, cellStyle).setCellValue("A4");
        createCellAddStype(row4, 15, cellStyle).setCellValue("A5");
        createCellAddStype(row4, 16, cellStyle).setCellValue("A6");
        createCellAddStype(row4, 17, cellStyle).setCellValue("A7");
        createCellAddStype(row4, 18, cellStyle).setCellValue("A8");
        createCellAddStype(row4, 19, cellStyle).setCellValue("A9");
        createCellAddStype(row4, 20, cellStyle).setCellValue("B1=a+b+c+d+e");
        createCellAddStype(row4, 21, cellStyle).setCellValue("B2=a+b+c+d+e+f");
        createCellAddStype(row4, 22, cellStyle).setCellValue("a");
        createCellAddStype(row4, 23, cellStyle).setCellValue("b");
        createCellAddStype(row4, 24, cellStyle).setCellValue("c");
        createCellAddStype(row4, 25, cellStyle).setCellValue("d");
        createCellAddStype(row4, 26, cellStyle).setCellValue("e=（a+b+c+d）*Ⓒ%");
        createCellAddStype(row4, 27, cellStyle).setCellValue("f=（a+b+c+d+e）*Ⓓ%");
        createCellAddStype(row4, 28, cellStyle).setCellValue("");
        createCellAddStype(row4, 29, cellStyle).setCellValue("C=A*B2");
        createCellAddStype(row4, 30, cellStyle).setCellValue("C1=A1*B2");
        createCellAddStype(row4, 31, cellStyle).setCellValue("C2=A2*B2");
        createCellAddStype(row4, 32, cellStyle).setCellValue("C3=A3*B2");
        createCellAddStype(row4, 33, cellStyle).setCellValue("C4=A4*B2");
        createCellAddStype(row4, 34, cellStyle).setCellValue("C5=A5*B2");
        createCellAddStype(row4, 35, cellStyle).setCellValue("C6=A6*B2");
        createCellAddStype(row4, 36, cellStyle).setCellValue("C7=A7*B2");
        createCellAddStype(row4, 37, cellStyle).setCellValue("C8=A8*B2");
        createCellAddStype(row4, 38, cellStyle).setCellValue("C9=A9*B2");
        createCellAddStype(row4, 39, cellStyle).setCellValue("备注");

        CellRangeAddress cra1 = new CellRangeAddress(1, 4, 0, 0);
        CellRangeAddress cra2 = new CellRangeAddress(1, 4,1, 1);
        CellRangeAddress cra3 = new CellRangeAddress(1, 4, 2, 2);
//        CellRangeAddress cra4 = new CellRangeAddress(1, 4, 3, 3);
//        CellRangeAddress cra5 = new CellRangeAddress(1, 4, 4, 4);
        CellRangeAddress cra6 = new CellRangeAddress(1, 4, 5, 5);
        CellRangeAddress cra7 = new CellRangeAddress(1, 4,6, 6);
        CellRangeAddress cra8 = new CellRangeAddress(1, 4,7, 7);
        CellRangeAddress cra9 = new CellRangeAddress(1, 4, 8, 8);
        CellRangeAddress cra10 = new CellRangeAddress(1, 4,9, 9);
        CellRangeAddress cra11 = new CellRangeAddress(1, 2,10, 19);
        CellRangeAddress cra12 = new CellRangeAddress(1, 2,20, 20);
        CellRangeAddress cra13 = new CellRangeAddress(1, 2, 21, 21);
        CellRangeAddress cra14 = new CellRangeAddress(1, 1, 22, 27);
        CellRangeAddress cra15 = new CellRangeAddress(2, 2, 22, 25);
        CellRangeAddress cra16 = new CellRangeAddress(1, 1, 28, 29);
        CellRangeAddress cra17 = new CellRangeAddress(1, 4, 39, 39);
        CellRangeAddress cra18 = new CellRangeAddress(1, 4, 3, 4);

        //在sheet里增加合并单元格
        sheet.addMergedRegion(cra1);
        sheet.addMergedRegion(cra2);
        sheet.addMergedRegion(cra3);
//        sheet.addMergedRegion(cra4);
//        sheet.addMergedRegion(cra5);
        sheet.addMergedRegion(cra6);
        sheet.addMergedRegion(cra7);
        sheet.addMergedRegion(cra8);
        sheet.addMergedRegion(cra9);
        sheet.addMergedRegion(cra10);
        sheet.addMergedRegion(cra11);
        sheet.addMergedRegion(cra12);
        sheet.addMergedRegion(cra13);
        sheet.addMergedRegion(cra14);
        sheet.addMergedRegion(cra15);
        sheet.addMergedRegion(cra16);
        sheet.addMergedRegion(cra17);
        sheet.addMergedRegion(cra18);

        Integer rowIndex = 5;
        for (Map.Entry<String, List<XfgcTabulation>> itemObj : nameType1List.entrySet()) {
            String nameType1 = itemObj.getKey();
            List<XfgcTabulation> value = itemObj.getValue();
            XfgcTabulation newT = calculationSubtotal(value);
            Row row5 = sheet.createRow(rowIndex);
            createCellAddStype(row5, 0, cellStyle).setCellValue(value.get(0).getCodeNumberType());
            createCellAddStype(row5, 1, cellStyle).setCellValue(nameType1);
            createCellAddStype(row5, 2, cellStyle).setCellValue("");
            createCellAddStype(row5, 3, cellStyle).setCellValue("");
            createCellAddStype(row5, 4, cellStyle).setCellValue("");
            createCellAddStype(row5, 5, cellStyle).setCellValue("");
            createCellAddStype(row5, 6, cellStyle).setCellValue("");
            createCellAddStype(row5, 7, cellStyle).setCellValue("");
            createCellAddStype(row5, 8, cellStyle).setCellValue("");
            createCellAddStype(row5, 9, cellStyle).setCellValue("");
            createCellAddStype(row5, 10, cellStyle).setCellValue("");
            createCellAddStype(row5, 11, cellStyle).setCellValue("");
            createCellAddStype(row5, 12, cellStyle).setCellValue("");
            createCellAddStype(row5, 13, cellStyle).setCellValue("");
            createCellAddStype(row5, 14, cellStyle).setCellValue("");
            createCellAddStype(row5, 15, cellStyle).setCellValue("");
            createCellAddStype(row5, 16, cellStyle).setCellValue("");
            createCellAddStype(row5, 17, cellStyle).setCellValue("");
            createCellAddStype(row5, 18, cellStyle).setCellValue("");
            createCellAddStype(row5, 19, cellStyle).setCellValue("");
            createCellAddStype(row5, 20, cellStyle).setCellValue("");
            createCellAddStype(row5, 21, cellStyle).setCellValue("");
            createCellAddStype(row5, 22, cellStyle).setCellValue("");
            createCellAddStype(row5, 23, cellStyle).setCellValue("");
            createCellAddStype(row5, 24, cellStyle).setCellValue("");
            createCellAddStype(row5, 25, cellStyle).setCellValue("");
            createCellAddStype(row5, 26, cellStyle).setCellValue("");
            createCellAddStype(row5, 27, cellStyle).setCellValue("");
            createCellAddStype(row5, 28, cellStyle).setCellValue(newT.getTotalPriceBhs() != null ? newT.getTotalPriceBhs().doubleValue() : 0);
            createCellAddStype(row5, 29, cellStyle).setCellValue(newT.getTotalPriceHs() != null ? newT.getTotalPriceHs().doubleValue() : 0);
            createCellAddStype(row5, 30, cellStyle).setCellValue(newT.getTotalPrice1() != null ? newT.getTotalPrice1().doubleValue() : 0);
            createCellAddStype(row5, 31, cellStyle).setCellValue(newT.getTotalPrice2() != null ? newT.getTotalPrice2().doubleValue() : 0);
            createCellAddStype(row5, 32, cellStyle).setCellValue(newT.getTotalPrice3() != null ? newT.getTotalPrice3().doubleValue() : 0);
            createCellAddStype(row5, 33, cellStyle).setCellValue(newT.getTotalPrice4() != null ? newT.getTotalPrice4().doubleValue() : 0);
            createCellAddStype(row5, 34, cellStyle).setCellValue(newT.getTotalPrice5() != null ? newT.getTotalPrice5().doubleValue() : 0);
            createCellAddStype(row5, 35, cellStyle).setCellValue(newT.getTotalPrice6() != null ? newT.getTotalPrice6().doubleValue() : 0);
            createCellAddStype(row5, 36, cellStyle).setCellValue(newT.getTotalPrice7() != null ? newT.getTotalPrice7().doubleValue() : 0);
            createCellAddStype(row5, 37, cellStyle).setCellValue(newT.getTotalPrice8() != null ? newT.getTotalPrice8().doubleValue() : 0);
            createCellAddStype(row5, 38, cellStyle).setCellValue(newT.getTotalPrice9() != null ? newT.getTotalPrice9().doubleValue() : 0);
            createCellAddStype(row5, 39, cellStyle).setCellValue("");

            for (XfgcTabulation item : value){
                rowIndex++;
                Row row = sheet.createRow(rowIndex);
                createCellAddStype(row, 0, cellStyle).setCellValue(item.getCodeNumber());
                createCellAddStype(row, 1, cellStyle).setCellValue(item.getName());
                createCellAddStype(row, 2, cellStyle).setCellValue(item.getXiTongFenLei());
                createCellAddStype(row, 3, cellStyle).setCellValue(item.getFenXiFenLei1());
                createCellAddStype(row, 4, cellStyle).setCellValue(item.getFenXiFenLei2());
                createCellAddStype(row, 5, cellStyle).setCellValue(item.getModelCode());
                createCellAddStype(row, 6, cellStyle).setCellValue(item.getTeZhengNeiRong());
                createCellAddStype(row, 7, cellStyle).setCellValue(item.getUnit());
                createCellAddStype(row, 8, cellStyle).setCellValue(item.getBrand());
                createCellAddStype(row, 9, cellStyle).setCellValue(item.getZhuCaiGongYing());
                createCellAddStype(row, 10, cellStyle).setCellValue(item.getGongChengLiangHuiZong() != null ? item.getGongChengLiangHuiZong().doubleValue() : 0);
                createCellAddStype(row, 11, cellStyle).setCellValue(item.getGongChengLiangDiXiShi() != null ? item.getGongChengLiangDiXiShi().doubleValue() : 0);
                createCellAddStype(row, 12, cellStyle).setCellValue(item.getGongChengLiangGaoCeng() != null ? item.getGongChengLiangGaoCeng().doubleValue() : 0);
                createCellAddStype(row, 13, cellStyle).setCellValue(item.getGongChengLiangXiaoGaoCeng() != null ? item.getGongChengLiangXiaoGaoCeng().doubleValue() : 0);
                createCellAddStype(row, 14, cellStyle).setCellValue(item.getGongChengLiangYangFang() != null ? item.getGongChengLiangYangFang().doubleValue() : 0);
                createCellAddStype(row, 15, cellStyle).setCellValue(item.getGongChengLiangShangYe() != null ? item.getGongChengLiangShangYe().doubleValue() : 0);
                createCellAddStype(row, 16, cellStyle).setCellValue(item.getGongChengLiangZongPing() != null ? item.getGongChengLiangZongPing().doubleValue() : 0);
                createCellAddStype(row, 17, cellStyle).setCellValue(item.getGongChengLiangBieShu() != null ? item.getGongChengLiangBieShu().doubleValue() : 0);
                createCellAddStype(row, 18, cellStyle).setCellValue(item.getGongChengLiangGongYu() != null ? item.getGongChengLiangGongYu().doubleValue() : 0);
                createCellAddStype(row, 19, cellStyle).setCellValue(item.getGongChengLiangYouErYuan() != null ? item.getGongChengLiangYouErYuan().doubleValue() : 0);
                createCellAddStype(row, 20, cellStyle).setCellValue(item.getComprehensivePriceBhs() != null ? item.getComprehensivePriceBhs().doubleValue() : 0);
                createCellAddStype(row, 21, cellStyle).setCellValue(item.getComprehensivePriceHs() != null ? item.getComprehensivePriceHs().doubleValue() : 0);
                createCellAddStype(row, 22, cellStyle).setCellValue(item.getRenGongCost() != null ? item.getRenGongCost().doubleValue() : 0);
                createCellAddStype(row, 23, cellStyle).setCellValue(item.getZhuCaiCost() != null ? item.getZhuCaiCost().doubleValue() : 0);
                createCellAddStype(row, 24, cellStyle).setCellValue(item.getFuCaiCost() != null ? item.getFuCaiCost().doubleValue() : 0);
                createCellAddStype(row, 25, cellStyle).setCellValue(item.getJiXieCost() != null ? item.getJiXieCost().doubleValue() : 0);
                createCellAddStype(row, 26, cellStyle).setCellValue(item.getQiYeCost() != null ? item.getQiYeCost().doubleValue() : 0);
                createCellAddStype(row, 27, cellStyle).setCellValue(item.getShuiJin() != null ? item.getShuiJin().doubleValue() : 0);
                createCellAddStype(row, 28, cellStyle).setCellValue(item.getTotalPriceBhs() != null ? item.getTotalPriceBhs().doubleValue() : 0);
                createCellAddStype(row, 29, cellStyle).setCellValue(item.getTotalPriceHs() != null ? item.getTotalPriceHs().doubleValue() : 0);
                createCellAddStype(row, 30, cellStyle).setCellValue(item.getTotalPrice1() != null ? item.getTotalPrice1().doubleValue() : 0);
                createCellAddStype(row, 31, cellStyle).setCellValue(item.getTotalPrice2() != null ? item.getTotalPrice2().doubleValue() : 0);
                createCellAddStype(row, 32, cellStyle).setCellValue(item.getTotalPrice3() != null ? item.getTotalPrice3().doubleValue() : 0);
                createCellAddStype(row, 33, cellStyle).setCellValue(item.getTotalPrice4() != null ? item.getTotalPrice4().doubleValue() : 0);
                createCellAddStype(row, 34, cellStyle).setCellValue(item.getTotalPrice5() != null ? item.getTotalPrice5().doubleValue() : 0);
                createCellAddStype(row, 35, cellStyle).setCellValue(item.getTotalPrice6() != null ? item.getTotalPrice6().doubleValue() : 0);
                createCellAddStype(row, 36, cellStyle).setCellValue(item.getTotalPrice7() != null ? item.getTotalPrice7().doubleValue() : 0);
                createCellAddStype(row, 37, cellStyle).setCellValue(item.getTotalPrice8() != null ? item.getTotalPrice8().doubleValue() : 0);
                createCellAddStype(row, 38, cellStyle).setCellValue(item.getTotalPrice9() != null ? item.getTotalPrice9().doubleValue() : 0);
                createCellAddStype(row, 39, cellStyle).setCellValue(item.getRemarks());
            }
            rowIndex++;
        }
        return workbook;
    }

    /**
     * 计算或提取基础信息
     * @param xfgcTabulations
     * @return
     */
    public StatisticalXfgcBiddingVo getInformation(List<XfgcTabulation> xfgcTabulations, Integer jieDuan){
        StatisticalXfgcBiddingVo vo = new StatisticalXfgcBiddingVo();
        BigDecimal totalPriceBhsCount = BigDecimal.ZERO;
        BigDecimal totalPriceHsCount = BigDecimal.ZERO;
        for (XfgcTabulation tabulation : xfgcTabulations){
            if (tabulation.getTotalPriceBhs() != null){
                totalPriceBhsCount = totalPriceBhsCount.add(tabulation.getTotalPriceBhs());
            }
            if (tabulation.getTotalPriceHs() != null){
                totalPriceHsCount = totalPriceHsCount.add(tabulation.getTotalPriceHs());
            }
        }
        if (!xfgcTabulations.isEmpty()) {
            vo.setXfgcBiddingCompanyId(xfgcTabulations.get(0).getXfgcBiddingCompany().getId());
            vo.setSupplyCompany(xfgcTabulations.get(0).getXfgcBiddingCompany().getSupplyCompany());
        }
        vo.setJieDuan(jieDuan);
        vo.setTotalPriceBhsCount(totalPriceBhsCount);
        vo.setTotalPriceHsCount(totalPriceHsCount);

        return vo;
    }


    /**
     * 计算税金、合价等
     * @param tabulation
     * @return
     */
    public XfgcTabulation calculation(XfgcTabulation tabulation){
        BigDecimal qiYe = BigDecimal.ZERO;
        BigDecimal shuiJin = BigDecimal.ZERO;
        BigDecimal comprehensivePriceBhs = BigDecimal.ZERO;
        BigDecimal comprehensivePriceHs = BigDecimal.ZERO;

        BigDecimal renGongCost = tabulation.getRenGongCost() != null ? tabulation.getRenGongCost() : BigDecimal.ZERO;
        BigDecimal zhuCaiCost = tabulation.getZhuCaiCost() != null ? tabulation.getZhuCaiCost() : BigDecimal.ZERO;
        BigDecimal fuCaiCost = tabulation.getFuCaiCost() != null ? tabulation.getFuCaiCost() : BigDecimal.ZERO;
        BigDecimal jiXieCost = tabulation.getJiXieCost() != null ? tabulation.getJiXieCost() : BigDecimal.ZERO;
        BigDecimal qiYeCost = tabulation.getQiYeCost() != null ? tabulation.getQiYeCost() : BigDecimal.ZERO;
        BigDecimal shuiJinCost = tabulation.getShuiJin() != null ? tabulation.getShuiJin() : BigDecimal.ZERO;

        BigDecimal qiYeCostFillIn = tabulation.getQiYeCostFillIn() != null ? tabulation.getQiYeCostFillIn() : BigDecimal.ZERO;
        BigDecimal shuiJinFillIn = tabulation.getShuiJinFillIn() != null ? tabulation.getShuiJinFillIn() : BigDecimal.ZERO;

        //企业管理
        qiYe = renGongCost.add(zhuCaiCost).add(fuCaiCost).add(jiXieCost).multiply(qiYeCostFillIn);
//        qiYe = qiYeCost;
        // 计算税金
        shuiJin = renGongCost.add(zhuCaiCost).add(fuCaiCost).add(jiXieCost).add(qiYe).multiply(shuiJinFillIn);
        // 计算综合单价-新项目不含增值税
        comprehensivePriceBhs = renGongCost.add(zhuCaiCost).add(fuCaiCost).add(jiXieCost).add(qiYe);
        // 计算综合单价-新项目含增值税
        comprehensivePriceHs = renGongCost.add(zhuCaiCost).add(fuCaiCost).add(jiXieCost).add(qiYe).add(shuiJin);

        tabulation.setQiYeCalcd(qiYe);
        tabulation.setShuiJinCalcd(shuiJin);
        tabulation.setComprehensivePriceBhsCalcd(comprehensivePriceBhs);
        tabulation.setComprehensivePriceHsCalcd(comprehensivePriceHs);
        return tabulation;
    }

    /**
     * 分项总计
     * @param tabulationList
     * @return
     */
    public XfgcTabulation calculationSubtotal(List<XfgcTabulation> tabulationList){
        XfgcTabulation newT = new XfgcTabulation();

        BigDecimal totalPriceBhsCount = BigDecimal.ZERO;
        BigDecimal totalPriceHsCount = BigDecimal.ZERO;
        BigDecimal totalPrice1Count = BigDecimal.ZERO;
        BigDecimal totalPrice2Count = BigDecimal.ZERO;
        BigDecimal totalPrice3Count = BigDecimal.ZERO;
        BigDecimal totalPrice4Count = BigDecimal.ZERO;
        BigDecimal totalPrice5Count = BigDecimal.ZERO;
        BigDecimal totalPrice6Count = BigDecimal.ZERO;
        BigDecimal totalPrice7Count = BigDecimal.ZERO;
        BigDecimal totalPrice8Count = BigDecimal.ZERO;
        BigDecimal totalPrice9Count = BigDecimal.ZERO;

        for (XfgcTabulation tabulation : tabulationList){
            totalPriceBhsCount = totalPriceBhsCount.add(tabulation.getTotalPriceBhs() != null ? tabulation.getTotalPriceBhs() : BigDecimal.ZERO);
            totalPriceHsCount = totalPriceHsCount.add(tabulation.getTotalPriceHs() != null ? tabulation.getTotalPriceHs() : BigDecimal.ZERO);
            totalPrice1Count = totalPrice1Count.add(tabulation.getTotalPrice1() != null ? tabulation.getTotalPrice1() : BigDecimal.ZERO);
            totalPrice2Count = totalPrice2Count.add(tabulation.getTotalPrice2() != null ? tabulation.getTotalPrice2() : BigDecimal.ZERO);
            totalPrice3Count = totalPrice3Count.add(tabulation.getTotalPrice3() != null ? tabulation.getTotalPrice3() : BigDecimal.ZERO);
            totalPrice4Count = totalPrice4Count.add(tabulation.getTotalPrice4() != null ? tabulation.getTotalPrice4() : BigDecimal.ZERO);
            totalPrice5Count = totalPrice5Count.add(tabulation.getTotalPrice5() != null ? tabulation.getTotalPrice5() : BigDecimal.ZERO);
            totalPrice6Count = totalPrice6Count.add(tabulation.getTotalPrice6() != null ? tabulation.getTotalPrice6() : BigDecimal.ZERO);
            totalPrice7Count = totalPrice7Count.add(tabulation.getTotalPrice7() != null ? tabulation.getTotalPrice7() : BigDecimal.ZERO);
            totalPrice8Count = totalPrice8Count.add(tabulation.getTotalPrice8() != null ? tabulation.getTotalPrice8() : BigDecimal.ZERO);
            totalPrice9Count = totalPrice9Count.add(tabulation.getTotalPrice9() != null ? tabulation.getTotalPrice9() : BigDecimal.ZERO);
        }

        newT.setName(tabulationList.get(0).getNameType1());
        newT.setNameType2("增补清单");
        newT.setTotalPriceBhs(totalPriceBhsCount);
        newT.setTotalPriceHs(totalPriceHsCount);
        newT.setTotalPrice1(totalPrice1Count);
        newT.setTotalPrice2(totalPrice2Count);
        newT.setTotalPrice3(totalPrice3Count);
        newT.setTotalPrice4(totalPrice4Count);
        newT.setTotalPrice5(totalPrice5Count);
        newT.setTotalPrice6(totalPrice6Count);
        newT.setTotalPrice7(totalPrice7Count);
        newT.setTotalPrice8(totalPrice8Count);
        newT.setTotalPrice9(totalPrice9Count);
        return newT;
    }

}
